package com.pk.platform.business.charge.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.pk.platform.business.charge.dao.ITuitionDetailDao;
import com.pk.platform.business.charge.vo.TuitionDetailList;
import com.pk.platform.business.charge.vo.TuitionDetailVO;
import com.pk.platform.business.core.dao.impl.GenericDaoImpl;
import com.pk.platform.util.StringConverter;
import com.pk.platform.util.page.ListPage;
import com.pk.platform.util.page.Pager;

public class TuitionDetailDaoImpl extends GenericDaoImpl implements
		ITuitionDetailDao {

	public int indexDetailCount(String indexId) {
		String sql = "select count(0) from tuition_detail where chargeIndexId = ?";
		return this.getSjt().queryForInt(sql, indexId);
	}
	
	public Double queryTotalAmount(String tuitionId)
	{
		String sql = "select sum(t2.amount) as total from tuition t1, tuition_detail t2 where t1.id = t2.tuitionId and t1.isDel = 0 and t1.id = ?";
		List<Map<String, Object>> list = this.getSjt().queryForList(sql, tuitionId);
		if(list.size() > 0)
		{
			Map<String, Object> map = list.get(0);
			if(map.get("total") != null)
			{
				return Double.parseDouble(map.get("total").toString());
			}
		}
		return 0.00;
	}

	public TuitionDetailList queryTuitionDetail(TuitionDetailVO tdvo, Pager pager)
	{
		StringBuffer sql = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		sql.append("select td.*, round(td.amount, 0) as intAmount, ci.indexName from tuition_detail td, charge_index ci where td.chargeIndexId = ci.id");
		StringBuffer condition = new StringBuffer();
		condition.append(" and td.tuitionId = ?");
		param.add(tdvo.getTd().getId());
		
		if(StringConverter.isNotNull(tdvo.getIndexId()))
		{
			condition.append(" and td.chargeIndexId = ?");
			param.add(tdvo.getIndexId());
		}
		if(StringConverter.isNotNull(tdvo.getYearMonth()))
		{
			condition.append(" and date_format(td.tuitionDateStart, '%Y-%m') <= ? and date_format(td.tuitionDateEnd, '%Y-%m') >= ?");
			param.add(tdvo.getYearMonth());
			param.add(tdvo.getYearMonth());
		}
		sql.append(condition.toString());
		sql.append(" order by td.year, td.month, ci.indexType, ci.indexName");
		ListPage lp = this.getListPage(sql.toString(), param.toArray(), pager);
		
		String sumSql = "select sum(amount) as total from tuition_detail td where 1 = 1";
		sumSql += condition.toString();
		List<Map<String, Object>> list = this.getSjt().queryForList(sumSql, param.toArray());
		String totalStr = "0.00";
		if(list.size() > 0)
		{
			Map<String, Object> map = list.get(0);
			if(map.get("total") != null)
			{
				totalStr = StringConverter.toNBitString(Double.parseDouble(map.get("total").toString()), 0);
			}
		}
		TuitionDetailList tdl = new TuitionDetailList();
		tdl.setListPage(lp);
		tdl.setTotalAmount(totalStr);
		return tdl;
	}
	
	public List<Map<String, Object>> queryYearOfOneTuition(String tuitionId)
	{
		String sql = "select distinct(t2.year) as year from tuition t1, tuition_detail t2 where t1.id = t2.tuitionId and t1.id = ? and t1.isDel = 0";
		return this.getSjt().queryForList(sql, tuitionId);
	}
	
	public ListPage queryThisMonthPayList(String childId, String year, String month, Pager pager)
	{
		StringBuffer sql = new StringBuffer();
		String yearMonth = year + "-" + month;
		sql.append("select t4.indexName, t2.tuitionDateStart, t2.tuitionDateEnd, t2.amount from tuition t1, tuition_detail t2, child t3, charge_index t4");
		sql.append(" where t1.id = t2.tuitionId and t1.childId = t3.id and t4.id = t2.chargeIndexId and t3.id = ?");
		sql.append(" and date_format(t2.tuitionDateStart, '%Y-%m') <= ? and date_format(t2.tuitionDateEnd, '%Y-%m') >= ? and t1.isDel = 0 order by t4.indexType, t4.indexName");
		List<Object> param = new ArrayList<Object>();
		param.add(childId);
		param.add(yearMonth);
		param.add(yearMonth);
		return this.getListPage(sql.toString(), param.toArray(), pager);
	}
	
}
